﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using System.IO;
using BL.Entities;
namespace zCO
{
    public class Excel
    {
        
        public static void export(DataGridView dgv)
        {
            generateExcel(dgv, false, false);   
        }

        public static void export(DataGridView dgv, bool pLandscape)
        {
            generateExcel(dgv, false, pLandscape);
        }


        public static void generateExcelFromFile(DataGridView dgv, bool imprimir, bool pLandscape,string NombreObra, string pMonth, string pYear){
           
#warning hay que achicar esta funcion.....

            double total = 0;
            int nroPag = 2; //El numero 1 viene harcodeado en la plantilla.
            string nroPrestador = "";

            try
            {
                nroPrestador = ColeccionObrasSociales.Current.GetByNombre(NombreObra).NroPrestador ;
            }
            catch (Exception)
            {
                
                
            }
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            if (imprimir)
            {
                excel.Visible = false;
            }
            Workbooks wbs = excel.Application.Workbooks;
            string planillaName =  "Planillas\\COZCO" + DateTime.Now.ToString("dd-MM-yyyy-hhmmss") + ".xls";
            File.Copy(System.AppDomain.CurrentDomain.BaseDirectory + "Planillas\\plantilla.xls", System.AppDomain.CurrentDomain.BaseDirectory + planillaName , true);
            Workbook wb = wbs.Open(System.AppDomain.CurrentDomain.BaseDirectory + planillaName );
            
            int ColumnIndex = 0;

            int rowIndex = 2;
            if (!imprimir) {
                excel.Visible = true;
            }

            excel.Cells[1, 2].Value = NombreObra;
            excel.Cells[1, 3].Value = "PRESTADOR Nº " + nroPrestador;
            excel.Cells[2, 5].Value = pMonth ;
            excel.Cells[2, 6].Value = pYear;
            foreach (DataGridViewRow row in dgv.Rows)
            {

                rowIndex++;
                if (((rowIndex+1) % 42) == 0)
                {
                    rowIndex++;
                    Worksheet ws = (Worksheet)excel.ActiveSheet;
                    ws.get_Range(excel.Cells[1, 1] as Range, excel.Cells[3, 8] as Range).Copy(excel.Cells[rowIndex , 1]);
                    
                    excel.Cells[rowIndex + 1, 8].Value = "HOJA Nº " + nroPag ;
                    
                    nroPag++;
                    rowIndex += 2;

                }

                ColumnIndex = 0;

                foreach (DataGridViewColumn col in dgv.Columns)
                {
                    if (col.Index != 0 && col.Index < 9)
                    {

                        ColumnIndex++;
                        //Seteo el formato precio
                        if (col.Index == 8)
                        {
                            if (row.Cells[col.Name].Value != null)
                            {
                                if (row.Cells[col.Name].Value.ToString().Contains(","))
                                {
                                    Worksheet ws = (Worksheet)excel.ActiveSheet;
                                    (excel.Cells[rowIndex + 1, ColumnIndex] as Range).NumberFormat = "$0.00";
                                }
                                else {
                                    (excel.Cells[rowIndex + 1, ColumnIndex] as Range).NumberFormat = "$0.00";
                                }
                                excel.Cells[rowIndex + 1, ColumnIndex] = Double.Parse(row.Cells[col.Name].Value.ToString());
                            }

                        }                     
                        if (row.Cells[col.Name].Value != null)
                        {
                            if (row.Cells[col.Name].Value.ToString().Contains("TOTAL"))
                            {
                                //excel.get_Range(excel.Cells[rowIndex + 1, ColumnIndex - 1], excel.Cells[rowIndex + 1, ColumnIndex]).Merge();
                                Worksheet ws = (Worksheet)excel.ActiveSheet;
                                ws.get_Range(excel.Cells[rowIndex + 1, ColumnIndex - 1] as Range, excel.Cells[rowIndex + 1, ColumnIndex] as Range).Merge(true);

                                //excel.get_Range(excel.Cells[10, 1] as Range , excel.Cells[10, 2] as Range ).Merge(true);
                                excel.Cells[rowIndex + 1, ColumnIndex - 1] = row.Cells[col.Name].Value;
                                excel.Cells[rowIndex + 1, ColumnIndex - 1].Font.Bold = true;
                                total += double.Parse(row.Cells[8].Value.ToString());

                            }
                            else if (col.Index != 8)
                            {
                               
                                //seteo el formato texto para el numero de cliente

                                if (ColumnIndex == 4)
                                {
                                    if (row.Cells[col.Name].Value != null)
                                    {
                                        Worksheet ws = (Worksheet)excel.ActiveSheet;
                                        (excel.Cells[rowIndex + 1, ColumnIndex] as Range).NumberFormat = "@";

                                        excel.Cells[rowIndex + 1, ColumnIndex] = row.Cells[col.Name].Value.ToString();
                                    }

                                }
                                else {
                                    excel.Cells[rowIndex + 1, ColumnIndex] = row.Cells[col.Name].Value;
                                }

                                if (excel.Cells[rowIndex, ColumnIndex].Value != null && excel.Cells[rowIndex + 1, ColumnIndex].Value != null)
                                {
                                    object c1 = excel.Cells[rowIndex, ColumnIndex].Value;
                                    object c2 = excel.Cells[rowIndex + 1, ColumnIndex].Value;

                                    if (c1.ToString()== c2.ToString() && col.Index  < 5)
                                    {
                                        excel.Cells[rowIndex + 1, ColumnIndex].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
                                    }
                                    
                                }                                
                                
                            }
                        }


                    }                    
                }

                              
                

            }
           

            Worksheet worksheet = (Worksheet)excel.ActiveSheet;
            if (pLandscape)
            {
                worksheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;
            }
            ColumnIndex = 0;
        
            if (imprimir)
            {
                worksheet.PrintOut();
                GC.Collect(); GC.WaitForPendingFinalizers();
                Marshal.FinalReleaseComObject(worksheet);
                wb.Close(false, Type.Missing, Type.Missing);
                Marshal.FinalReleaseComObject(wb);
                Marshal.FinalReleaseComObject(wbs); 
                Marshal.FinalReleaseComObject(excel);
            }
            else {

                GC.Collect(); 
                Marshal.FinalReleaseComObject(worksheet);                
                Marshal.FinalReleaseComObject(wb);
                Marshal.FinalReleaseComObject(wbs); 
                Marshal.FinalReleaseComObject(excel);
            }
         
            
        
        }
        

        public static void Imprimir(DataGridView dgv)
        {
            generateExcel(dgv, true, false);
        
        }



         private static void generateExcel(DataGridView dgv, bool imprimir, bool pLandscape){

            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            if (imprimir)
            {
                excel.Visible = false;
            }
            Workbooks wbs = excel.Application.Workbooks;
            Workbook wb = wbs.Add(true);
            
            // excel.Application.Workbooks.Add(true);

            int ColumnIndex = 0;

            foreach (DataGridViewColumn col in dgv.Columns)
            {
                ColumnIndex++;
                excel.Cells[1, ColumnIndex] = col.Name;
                excel.Cells[1, ColumnIndex].Font.Bold = true;
              //  excel.Cells[1, ColumnIndex].Borders.ThemeColor = Microsoft.Office.Interop.Excel.XlThemeColor.xlThemeColorDark1;
                excel.Cells[1, ColumnIndex].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                excel.Cells[1, ColumnIndex].Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
            }

            int rowIndex = 0;
            if (!imprimir) {
                excel.Visible = true;
            }
            foreach (DataGridViewRow row in dgv.Rows)
            {

                rowIndex++;
               
                ColumnIndex = 0;

                foreach (DataGridViewColumn col in dgv.Columns)
                {
                    ColumnIndex++;
                    excel.Cells[rowIndex + 1, ColumnIndex] = row.Cells[col.Name].Value;
                    excel.Cells[rowIndex + 1, ColumnIndex].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                    excel.Cells[rowIndex + 1, ColumnIndex].Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
                }
            }



            Worksheet worksheet = (Worksheet)excel.ActiveSheet;
            if (pLandscape)
            {
                worksheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;
            }
            ColumnIndex = 0;
            foreach (DataGridViewColumn col in dgv.Columns)
            {

                ColumnIndex++;
                int trash;
                int colWidth = Math.DivRem(col.Width, 6, out trash);
                SetColumnWidth(worksheet, ColumnIndex, colWidth);


            }
            if (imprimir)
            {
                worksheet.PrintOut();
                GC.Collect(); GC.WaitForPendingFinalizers();
                Marshal.FinalReleaseComObject(worksheet);
                wb.Close(false, Type.Missing, Type.Missing);
                Marshal.FinalReleaseComObject(wb);
                Marshal.FinalReleaseComObject(wbs); 
                Marshal.FinalReleaseComObject(excel);
            }
            else {

                GC.Collect(); 
                Marshal.FinalReleaseComObject(worksheet);                
                Marshal.FinalReleaseComObject(wb);
                Marshal.FinalReleaseComObject(wbs); 
                Marshal.FinalReleaseComObject(excel);
            }
         
            
        
        }
















        public static void Imprimir(DataGridView dgv,bool plandscape)
        {
            generateExcel(dgv, true, plandscape);

        }
       // private void PrintToFile() { // Make sure the worksheet has some data before printing. this.Range["A1", missing].Value2 = "123"; this.PrintOut(1, 2, 1, false, missing, true, false, missing); } 

      //   void PrintMyExcelFile() { Excel.Application excelApp = new Excel.Application(); // Open the Workbook: Excel.Workbook wb = excelApp.Workbooks.Open( @"C:\My Documents\Book1.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Type.Missing,Type.Missing); // Get the first worksheet. // (Excel uses base 1 indexing, not base 0.) Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1]; // Print out 1 copy to the default printer: ws.PrintOut( Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); // Cleanup: GC.Collect(); GC.WaitForPendingFinalizers(); Marshal.FinalReleaseComObject(ws); wb.Close(false, Type.Missing, Type.Missing); Marshal.FinalReleaseComObject(wb); excelApp.Quit(); Marshal.FinalReleaseComObject(excelApp); } 


        static void SetColumnWidth(Worksheet ws, int col, int width)
        {
            ((Range)ws.Cells[1, col]).EntireColumn.ColumnWidth = width;
        }
    }
}
